package net.bolue.sysmng.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.bolue.sysmng.db.metadata.ColMetaData;
import net.bolue.sysmng.db.metadata.PKeyMetaData;
import net.bolue.sysmng.db.metadata.TblMetaData;
import net.bolue.sysmng.exception.BolueException;

public class DBMetaManager {

	/**
	 * 获得数据库名
	 * @param subSys
	 * @return
	 */
	public static String getCatalog(String subSys)throws BolueException{
		Connection conn = DbConnector.getConnection(subSys);
		String catalog = null;
		try {
			catalog = conn.getCatalog();
		} catch (SQLException e) {
			throw new BolueException(9, "", e);
		}
		return catalog;
	}
	
	/**
	 * 获得所有表
	 * @param subSys
	 * @param catalog
	 * @param schema
	 * @return
	 * @throws BolueException
	 */
	public static List<TblMetaData> getAllTables(String subSys, String catalog, String schema) throws BolueException{
		Connection conn = DbConnector.getConnection(subSys);
		List<TblMetaData> tableList = new ArrayList<TblMetaData>();
		try{
			ResultSet rs = conn.getMetaData().getTables(catalog, null, null, new String[]{"TABLE"});
			while (rs.next()) {
				String tableCat = rs.getString("TABLE_CAT");
				String tableSchema = rs.getString("TABLE_SCHEM");
				String tableName = rs.getString("TABLE_NAME");
				String tableType = rs.getString("TABLE_TYPE");
				String remarks = rs.getString("REMARKS");
				TblMetaData tblMetaData = new TblMetaData();
				tblMetaData.setTableName(tableName);
				tblMetaData.setTableCat(tableCat);
				tblMetaData.setTableSchema(tableSchema);
				tblMetaData.setTableType(tableType);
				tblMetaData.setRemarks(remarks);
				tableList.add(tblMetaData);
			}
		} catch (SQLException e) {
			throw new BolueException(9, "", e);
		}
		return tableList;
	}
	
	/**
	 * 获得所有表列
	 * @param subSys
	 * @param tblName
	 * @return
	 * @throws BolueException
	 */
	public static List<ColMetaData> getAllColumns(String subSys, String tblName)throws BolueException{
		Connection conn = DbConnector.getConnection(subSys);
		String catalog = getCatalog(subSys);
		List<ColMetaData> columnList = new ArrayList<ColMetaData>();
		try{
			ResultSet rs = conn.getMetaData().getColumns(catalog, null, tblName, null);
			while (rs.next()) {
				ColMetaData colMetaData = new ColMetaData();
				String tableCat = rs.getString("TABLE_CAT");
				String tableSchema = rs.getString("TABLE_SCHEM");
				String tableName = rs.getString("TABLE_NAME");
				String columnName = rs.getString("COLUMN_NAME");
				Integer dataType = rs.getInt("DATA_TYPE");
				String typeName = rs.getString("TYPE_NAME");
				Integer columnSize = rs.getInt("COLUMN_SIZE");
				String isNullable = rs.getString("IS_NULLABLE");
				Integer nullAble = rs.getInt("NULLABLE");
				Integer ordinalPosition = rs.getInt("ORDINAL_POSITION");
				String columnDef = rs.getString("COLUMN_DEF");
				Integer charOctelLength = rs.getInt("CHAR_OCTET_LENGTH");
				String remarks = rs.getString("REMARKS");
				colMetaData.setTableCat(tableCat);
				colMetaData.setTableSchema(tableSchema);
				colMetaData.setTableName(tableName);
				colMetaData.setColumnName(columnName);
				colMetaData.setDataType(dataType);
				colMetaData.setTypeName(typeName);
				colMetaData.setColumnSize(columnSize);
				colMetaData.setIsNullable(isNullable);
				colMetaData.setNullable(nullAble);
				colMetaData.setOrdinalPosition(ordinalPosition);
				colMetaData.setColumnDef(columnDef);
				colMetaData.setCharOctetLength(charOctelLength);
				colMetaData.setRemarks(remarks);
				columnList.add(colMetaData);
			}
		} catch (SQLException e) {
			throw new BolueException(9, "", e);
		}
		return columnList;
	}
	
	/**
	 * 获得表的主键
	 * @param subSys
	 * @param tblName
	 * @return
	 * @throws BolueException
	 */
	public static List<PKeyMetaData> getPKeyColumns(String subSys, String tblName)throws BolueException{
		Connection conn = DbConnector.getConnection(subSys);
		String catalog = getCatalog(subSys);
		List<PKeyMetaData> pkList = new ArrayList<PKeyMetaData>();
		try{
			ResultSet rs = conn.getMetaData().getPrimaryKeys(catalog, null, tblName);
			while (rs.next()) {
				String tableCat = rs.getString("TABLE_CAT");
				String tableSchema = rs.getString("TABLE_SCHEM");
				String tableName = rs.getString("TABLE_NAME");
				String columnName = rs.getString("COLUMN_NAME");
				Short keySeq = rs.getShort("KEY_SEQ");
				String pkName = rs.getString("PK_NAME");
				PKeyMetaData pKeyMetaData = new PKeyMetaData();
				pKeyMetaData.setColumnName(columnName);
				pKeyMetaData.setKeySeq(keySeq.intValue());
				pKeyMetaData.setPkName(pkName);
				pKeyMetaData.setTableCat(tableCat);
				pKeyMetaData.setTableName(tableName);
				pKeyMetaData.setTableSchema(tableSchema);
				pkList.add(pKeyMetaData);
			}
		} catch (SQLException e) {
			throw new BolueException(9, "", e);
		}
		return pkList;
	}
	
	public static Map<String,PKeyMetaData> getPKeyColumnsMap(String subSys, String tblName)throws BolueException{
		List<PKeyMetaData> pkList = getPKeyColumns(subSys,tblName);
		if(pkList==null){
			return null;
		}
		Map<String,PKeyMetaData> map = new HashMap<String,PKeyMetaData>();
		for(PKeyMetaData pKeyMetaData : pkList){
			map.put(pKeyMetaData.getColumnName(), pKeyMetaData);
		}
		return map;
	}
}
